package com.supermap.dataservice.farmland.dao;

import com.supermap.dubbo.api.farmland.entity.Farmland;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @Author:langhongshuai
 * @Description:农田数据访问层
 * @Date:18/1/28 下午10:52
 */
@Mapper
public interface FarmlandDao {
    /**
     * 获得用户的所有农田信息
     * @return
     */
    @SelectProvider(type =FarmlandDaoSQL.class,method = "getFarmlandByUIdAreaSQL")
    public List<Farmland> getFarmlandByUIdArea(@Param("userId") String userId, @Param("areaCode") String areaCode,@Param("pageNum") Integer pageNum,@Param("pageSize") Integer pageSize);

    @SelectProvider(type =FarmlandDaoSQL.class,method = "getFarmlandByUIdAreaCountNumSQL")
    Integer getFarmlandByUIdAreaCountNum(@Param("userId") String userId, @Param("areaCode") String areaCode);


    class  FarmlandDaoSQL{
      public String  getFarmlandByUIdAreaSQL(Map<String,Object> map){
         String userId=(String)map.get("userId");
          String areaCode=(String)map.get("areaCode");
          Integer pageNum=(Integer) map.get("pageNum");
          Integer pageSize=(Integer)map.get("pageSize");
          StringBuilder stringBuilder=new StringBuilder();
          stringBuilder.append(" select t1.*,t6.areaname,t4.name,t4.crop,t5.username,t4.parentid as parentcrop,t4.img_ico as cropicon from t_farmland t1 inner JOIN t_area t6 on t1.`code`=t6.areacode inner JOIN t_user t5 on t1.userId=t5.id inner JOIN(select t2.*,t3.name,t3.img_ico,if(t3.parentid=0,t3.code,t3.parentid) as parentid from t_farmcrops t2 left JOIN t_crop t3 on t2.crop=t3.code) t4 on t1.id=t4.farmland where 1=1 ");
          if(userId!=null){
                stringBuilder.append( " and t1.userId="+userId+" ");
          }
          if(areaCode!=null){
              stringBuilder.append( " and t1.code like concat("+areaCode+",'%') ");
          }
          if(pageNum!=null&&pageSize!=null ){
              stringBuilder.append( " limit "+(pageNum-1)*pageSize+","+pageSize+" ");
          }

          return  stringBuilder.toString();
        }
        public String  getFarmlandByUIdAreaCountNumSQL(Map<String,Object> map){
            String userId=(String)map.get("userId");
            String areaCode=(String)map.get("areaCode");
            StringBuilder stringBuilder=new StringBuilder();
            stringBuilder.append(" select count(1)  from t_farmland t1 inner JOIN t_area t6 on t1.`code`=t6.areacode inner JOIN t_user t5 on t1.userId=t5.id inner JOIN(select t2.*,t3.name,if(t3.parentid=0,t3.code,t3.parentid) as parentid from t_farmcrops t2 left JOIN t_crop t3 on t2.crop=t3.code) t4 on t1.id=t4.farmland where 1=1 ");
            if(userId!=null){
                stringBuilder.append( " and t1.userId="+userId+" ");
            }
            if(areaCode!=null){
                stringBuilder.append( " and t1.code like concat("+areaCode+",'%') ");
            }


            return  stringBuilder.toString();
        }
    }

    /**
     * 增加农田
     * @param farmland
     */
    @Insert("Insert into t_farmland(userId,coordinates,descript,height,longitude,latitude,area,code,farmname)\n " +
            "values(#{farmland.userId},#{farmland.coordinates},#{farmland.descript},\n" +
            "#{farmland.height},#{farmland.longitude},#{farmland.latitude},#{farmland.area},#{farmland.code},#{farmland.farmname} \n" +
            ")")
    public void addFarmland(@Param("farmland") Farmland farmland);

    /**
     * 增加农田作物关联
     * @param crop
     * @param farmland
     */
    @Insert("Insert into t_farmcrops(crop,farmland) values(#{crop},#{farmland})")
    public void addFarmCrops(@Param("farmland") int farmland, @Param("crop") String crop);

    /**
     * 获取刚刚插入的id
     */
    @Select("Select max(id) from t_farmland")
    public int getFarmId();

    /**
     * 获得用户的所有农田信息
     * @return
     * @param areaCodes
     */
    @Select("<script>" +
            "SELECT COUNT(t1.id) AS count, t1.code AS areaCode,t_area.areaname AS areaName FROM t_farmland t1 \n" +
            "JOIN t_area ON t_area.areacode = t1.code\n" +
            "WHERE t1.del_flg = 0 \n" +
            "<if test='areaCodes != null'>\n" +
            "AND t1.code IN\n" +
            "<foreach collection='areaCodes' open='(' close=')' item='areaCode' separator=','>\n" +
            "#{areaCode}\n" +
            "</foreach>\n" +
            "</if>" +
            "GROUP BY t1.code" +
            "</script>")
    List<Map> getFarmlandCountByArea(@Param("areaCodes") String[] areaCodes);

    /**
     * 删除农田
     * @param farmlandId
     */
    @Update("Update  t_farmland set del_flg=1   where id=#{farmlandId}")
    public void delFarmland(@Param("farmlandId") int farmlandId);

    /**
     * 通过作物id和区域code获取农田信息
     * @param cropId
     * @param areaCode
     */
    @Select("SELECT t1.*, t2.crop,t3.username,t4.areaname FROM\n"
            + "t_farmland t1\n"
            + "LEFT JOIN t_farmcrops t2 ON t1.id = t2.farmland\n"
            + "LEFT JOIN t_user t3 ON t1.userId = t3.id\n"
            + "LEFT JOIN t_area t4 ON t4.areacode = t1.code\n"
            + "left join t_crop t5 on t2.crop in(select id from t_crop "
            + "where id in(select id from t_crop where parentid = t5.id or id=t5.id) "
            + "or parentid in (select id from t_crop where parentid = t5.id )) "
            + "where if((isnull(#{cropId}) or #{cropId}=''),true,t2.crop in(select id from t_crop where id in(select id from t_crop where parentid = #{cropId} or id= #{cropId}) or\n"
            + " parentid in (select id from t_crop where parentid = #{cropId}  ))) and if(isnull(#{areaCode}),true,t1.code like CONCAT(#{areaCode},'%'))")
    public List<Farmland> getFramlandByCropIdAndAreaCode(@Param("cropId") String cropId, @Param("areaCode") String areaCode);

    /**
     * 通过区域获取农田
     * @param areaCode
     * @return
     */
    @Select("select t1.*,t2.areaname from t_farmland t1 LEFT JOIN t_area t2 ON t2.areacode = t1.code where t1.code like CONCAT(#{areaCode},'%')")
    public List<Farmland> getAllFarmland(@Param("areaCode") String areaCode);


    /**
     * 获得用户的所有农田信息
     * @return
     */
    @Select("select sum(t1.area) as area,\n" +
            "\tt1.*,\n" +
            "\tt4. NAME ,\n" +
            "t4.parentid AS parentcrop ,\n" +
            "\tt4.crop," +
            "\tt5.username,\n" +
            "\tGROUP_CONCAT(t6.areaname) as areaname " +
            " from t_farmland t1 " +
            " inner JOIN t_area t6 on t1.`code`=t6.areacode "+
            "inner JOIN t_user t5 on t1.userId=t5.id inner JOIN" +
            "(select t2.*,t3.name,if(t3.parentid=0,t3.`code`,t3.parentid) as parentid from t_farmcrops t2 left JOIN t_crop t3 on t2.crop=t3.code) t4\n" +
            "on t1.id=t4.farmland where IF(isnull(#{userId}),true,t1.userId=#{userId}) group by crop")
    public List<Farmland> getFarmlandByUId(@Param("userId") String userId);


    /**
     * 更新famland信息
     * @param farmland
     */
    @Update("update t_farmland f,t_farmcrops fc set fc.crop = #{Farmland.crop},f.userId=#{Farmland.userId}, " +
            "f.coordinates=#{Farmland.coordinates},f.descript=#{Farmland.descript}," +
            "f.longitude=#{Farmland.longitude},f.latitude=#{Farmland.latitude},f.area=#{Farmland.area}," +
            "f.code=#{Farmland.code},f.farmname=#{Farmland.farmname} where fc.farmland=#{Farmland.id} and f.id=#{Farmland.id}")
    public void updateFarmland(@Param("Farmland")Farmland farmland);


    /**
     * 通过区域获取产业布局
     * @return
     */
    @Select("select t1.V_YEAR,t1.C56001 crop,t1.C_COUNTY county,t1.C_TOWN town,t2.coordinates,\n" +
            "if(t1.V56180 = 0.0000,null, round(V56180,2)) V56180,\n" +
            "if(t1.V56181 = 0.0000,null, round(V56181,2)) V56181,\n" +
            "if(t1.V56182 = 0.0000,null, round(V56182,2)) V56182 \n" +
            "from cagmpisards.t_h_econ_yield_county t1 \n" +
            "inner join cagmpisards.t_area_town t2 on t1.areaCode = left(t2.areacode,4) and position(t1.C_TOWN in t2.areaname) \n" +
            "where t1.V_YEAR = #{year} and if(#{crop} is null,1=1,find_in_set(t1.C56001,#{crop}))  and t1.areaCode like concat(#{areaCode},'%') and trim(t1.C_TOWN) <> '' \n" +
            ";")
    List<Map<String,Object>> getDistributionByArea(@Param("year") String year,@Param("areaCode") String areaCode,@Param("crop") String crop);

}

